package com.xzcedu.jdbc.datasource;

import com.xzcedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * @author 徐州城
 * @version 1.0
 */
public class JDBCUtilsByDruid_USE {

    @Test
    @SuppressWarnings("all")
    public void testSelect() {
        System.out.println("使用Druid方式");
        //1. 得到连接
        Connection connection = null;

        //2. 组织sql语句
        String sql = "select * from actor";

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //class com.alibaba.druid.pool.DruidPooledConnection
            System.out.println(connection.getClass());
            preparedStatement = connection.prepareStatement(sql);
            //执行
            resultSet = preparedStatement.executeQuery();

            //遍历结果集
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        }
    }


    //使用土方法解决ResultSet =封装=> ArrayList
    @Test
    @SuppressWarnings("all")
    public /*ArrayList<Actor>*/ void testSelectToArrayList() {
        System.out.println("使用Druid方式");
        //1. 得到连接
        Connection connection = null;

        //2. 组织sql语句
        String sql = "select * from actor";

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Actor> list = new ArrayList<Actor>();//创建ArrayList对象，存放Actor对象

        //3. 创建PreparedStatement 对象
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //class com.alibaba.druid.pool.DruidPooledConnection
            System.out.println(connection.getClass());
            preparedStatement = connection.prepareStatement(sql);
            //执行
            resultSet = preparedStatement.executeQuery();

            //遍历结果集
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                //把得到的resultSet的记录 封装到 Actor对象，放入到list集合
                list.add(new Actor(id, name, sex, borndate, phone));
            }

            System.out.println("list集合数据=" + list);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
            //因为ArrayList 和 connection 没有任何关联，所以该集合可以复用.
            //return list;
        }

    }


}
